/**
 * 数据库同步脚本：将主数据库内容同步到备份数据库
 * 支持全量同步和增量同步
 */

const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');

// 数据库配置
const SOURCE_DB = {
  host: '43.143.253.188',
  port: 3306,
  user: 'nextjs',
  password: 'yEAiRE2jcxTyRBy5',
  database: 'nextjs',
  charset: 'utf8mb4'
};

const BACKUP_DB = {
  host: '49.233.244.230', // 备份数据库地址
  port: 3306,
  user: 'nextjs',
  password: 'yEAiRE2jcxTyRBy5',
  database: 'nextjs',
  charset: 'utf8mb4'
};

class DatabaseSync {
  constructor() {
    this.sourceConnection = null;
    this.backupConnection = null;
    this.syncLog = [];
    this.startTime = new Date();
  }

  async connect() {
    try {
      console.log('🔄 连接源数据库...');
      this.sourceConnection = await mysql.createConnection(SOURCE_DB);
      console.log('✅ 源数据库连接成功');

      console.log('🔄 连接备份数据库...');
      this.backupConnection = await mysql.createConnection(BACKUP_DB);
      console.log('✅ 备份数据库连接成功');

      // 设置字符集和时区
      await this.sourceConnection.execute("SET NAMES utf8mb4");
      await this.sourceConnection.execute("SET time_zone = '+08:00'");
      await this.backupConnection.execute("SET NAMES utf8mb4");
      await this.backupConnection.execute("SET time_zone = '+08:00'");

    } catch (error) {
      console.error('❌ 数据库连接失败:', error);
      throw error;
    }
  }

  async disconnect() {
    if (this.sourceConnection) {
      await this.sourceConnection.end();
      console.log('✅ 源数据库连接已关闭');
    }
    if (this.backupConnection) {
      await this.backupConnection.end();
      console.log('✅ 备份数据库连接已关闭');
    }
  }

  async getBackupTableList() {
    try {
      const [tables] = await this.backupConnection.execute(
        `
        SELECT TABLE_NAME 
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = ? 
        AND TABLE_TYPE = 'BASE TABLE'
        ORDER BY TABLE_NAME
      `,
        [BACKUP_DB.database]
      );
      return tables.map((row) => row.TABLE_NAME);
    } catch (error) {
      console.error('❌ 获取备份库表列表失败:', error);
      throw error;
    }
  }

  async dropAllBackupTables() {
    try {
      console.log('🧹 开始清理备份库所有表...');
      await this.backupConnection.execute('SET FOREIGN_KEY_CHECKS = 0');
      const tables = await this.getBackupTableList();
      for (const tableName of tables) {
        try {
          await this.backupConnection.execute(`DROP TABLE IF EXISTS \`${tableName}\``);
          console.log(`🗑️ 已删除备份表 ${tableName}`);
        } catch (error) {
          console.error(`❌ 删除备份表 ${tableName} 失败:`, error.message);
        }
      }
      await this.backupConnection.execute('SET FOREIGN_KEY_CHECKS = 1');
      console.log('✅ 备份库清理完成');
    } catch (error) {
      console.error('❌ 清理备份库失败:', error);
      throw error;
    }
  }

  async getTableList() {
    try {
      const [tables] = await this.sourceConnection.execute(`
        SELECT TABLE_NAME 
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = ? 
        AND TABLE_TYPE = 'BASE TABLE'
        ORDER BY TABLE_NAME
      `, [SOURCE_DB.database]);

      return tables.map(row => row.TABLE_NAME);
    } catch (error) {
      console.error('❌ 获取表列表失败:', error);
      throw error;
    }
  }

  async getTableStructure(tableName) {
    try {
      const [columns] = await this.sourceConnection.execute(`
        SELECT 
          COLUMN_NAME,
          DATA_TYPE,
          IS_NULLABLE,
          COLUMN_DEFAULT,
          COLUMN_KEY,
          EXTRA,
          COLUMN_COMMENT
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?
        ORDER BY ORDINAL_POSITION
      `, [SOURCE_DB.database, tableName]);

      return columns;
    } catch (error) {
      console.error(`❌ 获取表 ${tableName} 结构失败:`, error);
      throw error;
    }
  }

  async createTableIfNotExists(tableName) {
    try {
      // 获取创建表的SQL语句
      const [createTableResult] = await this.sourceConnection.execute(`
        SHOW CREATE TABLE \`${tableName}\`
      `);

      const createTableSQL = createTableResult[0]['Create Table'];

      // 在备份数据库中创建表
      await this.backupConnection.execute(createTableSQL);

      console.log(`✅ 表 ${tableName} 结构同步完成`);
      return true;
    } catch (error) {
      console.error(`❌ 创建表 ${tableName} 失败:`, error);
      return false;
    }
  }

  async getTableRowCount(tableName) {
    try {
      const [result] = await this.sourceConnection.execute(`SELECT COUNT(*) as count FROM \`${tableName}\``);
      return result[0].count;
    } catch (error) {
      console.error(`❌ 获取表 ${tableName} 行数失败:`, error);
      return 0;
    }
  }

  async syncTableData(tableName, batchSize = 1000) {
    try {
      console.log(`🔄 开始同步表 ${tableName} 的数据...`);

      // 获取表的总行数
      const totalRows = await this.getTableRowCount(tableName);
      console.log(`📊 表 ${tableName} 总行数: ${totalRows}`);

      if (totalRows === 0) {
        console.log(`ℹ️ 表 ${tableName} 无数据，跳过同步`);
        return { success: true, rows: 0 };
      }

      // 清空备份表中的数据
      await this.backupConnection.execute(`TRUNCATE TABLE \`${tableName}\``);
      console.log(`🗑️ 已清空备份表 ${tableName}`);

      let syncedRows = 0;
      let offset = 0;

      while (offset < totalRows) {
        // 分批获取数据
        const [rows] = await this.sourceConnection.execute(`
          SELECT * FROM \`${tableName}\` 
          LIMIT ${batchSize} OFFSET ${offset}
        `);

        if (rows.length === 0) break;

        // 获取列名
        const columns = Object.keys(rows[0]);
        const placeholders = columns.map(() => '?').join(', ');
        const columnNames = columns.map(col => `\`${col}\``).join(', ');

        // 构建批量插入SQL
        const insertSQL = `INSERT INTO \`${tableName}\` (${columnNames}) VALUES (${placeholders})`;

        // 准备数据
        const values = rows.map(row => columns.map(col => row[col]));

        // 批量插入
        for (const rowValues of values) {
          await this.backupConnection.execute(insertSQL, rowValues);
        }

        syncedRows += rows.length;
        offset += batchSize;

        const progress = Math.round((syncedRows / totalRows) * 100);
        console.log(`📈 表 ${tableName} 同步进度: ${syncedRows}/${totalRows} (${progress}%)`);
      }

      console.log(`✅ 表 ${tableName} 数据同步完成，共同步 ${syncedRows} 行`);
      return { success: true, rows: syncedRows };

    } catch (error) {
      // 若目标表不存在，尝试重建一次并重试
      if (error && (error.code === 'ER_NO_SUCH_TABLE' || /doesn't exist/i.test(error.sqlMessage || ''))) {
        console.warn(`⚠️ 目标表 ${tableName} 不存在，尝试重建后重试...`);
        const ok = await this.createTableIfNotExists(tableName);
        if (ok) {
          return await this.syncTableData(tableName, batchSize);
        }
      }
      console.error(`❌ 同步表 ${tableName} 数据失败:`, error);
      return { success: false, error: error.message };
    }
  }

  async syncAllTables() {
    try {
      console.log('🔄 开始全量数据库同步...');

      // 全量同步前：清空备份数据库的所有表，避免已存在导致失败
      await this.dropAllBackupTables();

      // 同步期间禁用外键约束，避免插入顺序导致的外键错误
      await this.backupConnection.execute('SET FOREIGN_KEY_CHECKS = 0');

      // 获取所有表
      const tables = await this.getTableList();
      console.log(`📋 发现 ${tables.length} 个表需要同步`);

      const syncResults = {
        total: tables.length,
        success: 0,
        failed: 0,
        details: []
      };

      for (const tableName of tables) {
        console.log(`\n🔄 处理表: ${tableName}`);

        try {
          // 1. 同步表结构
          const structureSuccess = await this.createTableIfNotExists(tableName);
          if (!structureSuccess) {
            syncResults.failed++;
            syncResults.details.push({
              table: tableName,
              status: 'failed',
              error: '表结构创建失败'
            });
            continue;
          }

          // 2. 同步表数据
          const dataResult = await this.syncTableData(tableName);
          if (dataResult.success) {
            syncResults.success++;
            syncResults.details.push({
              table: tableName,
              status: 'success',
              rows: dataResult.rows
            });
          } else {
            syncResults.failed++;
            syncResults.details.push({
              table: tableName,
              status: 'failed',
              error: dataResult.error
            });
          }

        } catch (error) {
          console.error(`❌ 处理表 ${tableName} 时发生错误:`, error);
          syncResults.failed++;
          syncResults.details.push({
            table: tableName,
            status: 'failed',
            error: error.message
          });
        }
      }

      // 同步完成后恢复外键约束
      await this.backupConnection.execute('SET FOREIGN_KEY_CHECKS = 1');
      return syncResults;

    } catch (error) {
      console.error('❌ 全量同步失败:', error);
      throw error;
    }
  }

  async syncSpecificTables(tableNames) {
    try {
      console.log(`🔄 开始同步指定表: ${tableNames.join(', ')}`);

      const syncResults = {
        total: tableNames.length,
        success: 0,
        failed: 0,
        details: []
      };

      for (const tableName of tableNames) {
        console.log(`\n🔄 处理表: ${tableName}`);

        try {
          // 1. 同步表结构
          const structureSuccess = await this.createTableIfNotExists(tableName);
          if (!structureSuccess) {
            syncResults.failed++;
            syncResults.details.push({
              table: tableName,
              status: 'failed',
              error: '表结构创建失败'
            });
            continue;
          }

          // 2. 同步表数据
          const dataResult = await this.syncTableData(tableName);
          if (dataResult.success) {
            syncResults.success++;
            syncResults.details.push({
              table: tableName,
              status: 'success',
              rows: dataResult.rows
            });
          } else {
            syncResults.failed++;
            syncResults.details.push({
              table: tableName,
              status: 'failed',
              error: dataResult.error
            });
          }

        } catch (error) {
          console.error(`❌ 处理表 ${tableName} 时发生错误:`, error);
          syncResults.failed++;
          syncResults.details.push({
            table: tableName,
            status: 'failed',
            error: error.message
          });
        }
      }

      return syncResults;

    } catch (error) {
      console.error('❌ 指定表同步失败:', error);
      throw error;
    }
  }

  async generateSyncReport(syncResults) {
    const endTime = new Date();
    const duration = Math.round((endTime - this.startTime) / 1000);

    const report = {
      syncInfo: {
        startTime: this.startTime.toISOString(),
        endTime: endTime.toISOString(),
        duration: `${duration}秒`,
        sourceDB: SOURCE_DB,
        backupDB: BACKUP_DB
      },
      summary: {
        totalTables: syncResults.total,
        successTables: syncResults.success,
        failedTables: syncResults.failed,
        successRate: Math.round((syncResults.success / syncResults.total) * 100)
      },
      details: syncResults.details
    };

    // 保存报告到文件
    const reportPath = path.join(__dirname, `sync-report-${this.startTime.toISOString().replace(/[:.]/g, '-')}.json`);
    fs.writeFileSync(reportPath, JSON.stringify(report, null, 2));

    console.log('\n📊 同步报告:');
    console.log(`  总表数: ${report.summary.totalTables}`);
    console.log(`  成功: ${report.summary.successTables}`);
    console.log(`  失败: ${report.summary.failedTables}`);
    console.log(`  成功率: ${report.summary.successRate}%`);
    console.log(`  耗时: ${report.syncInfo.duration}`);
    console.log(`  报告文件: ${reportPath}`);

    if (syncResults.failed > 0) {
      console.log('\n❌ 失败的表:');
      syncResults.details
        .filter(detail => detail.status === 'failed')
        .forEach(detail => {
          console.log(`  - ${detail.table}: ${detail.error}`);
        });
    }

    return report;
  }

  async verifySync() {
    try {
      console.log('\n🔍 开始验证同步结果...');

      const tables = await this.getTableList();
      const verificationResults = [];

      for (const tableName of tables) {
        try {
          // 获取源表行数
          const [sourceCount] = await this.sourceConnection.execute(`SELECT COUNT(*) as count FROM \`${tableName}\``);
          const sourceRows = sourceCount[0].count;

          // 获取备份表行数
          const [backupCount] = await this.backupConnection.execute(`SELECT COUNT(*) as count FROM \`${tableName}\``);
          const backupRows = backupCount[0].count;

          const isMatch = sourceRows === backupRows;
          verificationResults.push({
            table: tableName,
            sourceRows,
            backupRows,
            match: isMatch
          });

          console.log(`  ${tableName}: 源表${sourceRows}行, 备份表${backupRows}行 ${isMatch ? '✅' : '❌'}`);

        } catch (error) {
          console.error(`  ❌ 验证表 ${tableName} 失败:`, error.message);
          verificationResults.push({
            table: tableName,
            error: error.message
          });
        }
      }

      const matchedTables = verificationResults.filter(r => r.match).length;
      console.log(`\n📊 验证结果: ${matchedTables}/${tables.length} 个表数据匹配`);

      return verificationResults;

    } catch (error) {
      console.error('❌ 验证同步结果失败:', error);
      throw error;
    }
  }
}

// 主函数
async function main() {
  const sync = new DatabaseSync();

  try {
    // 连接数据库
    await sync.connect();

    // 解析命令行参数
    const args = process.argv.slice(2);
    const command = args[0] || 'all';

    let syncResults;

    if (command === 'all') {
      // 全量同步
      syncResults = await sync.syncAllTables();
    } else if (command === 'tables') {
      // 同步指定表
      const tableNames = args.slice(1);
      if (tableNames.length === 0) {
        console.error('❌ 请指定要同步的表名');
        process.exit(1);
      }
      syncResults = await sync.syncSpecificTables(tableNames);
    } else if (command === 'verify') {
      // 仅验证
      await sync.verifySync();
      return;
    } else {
      console.error('❌ 无效的命令');
      console.log('用法:');
      console.log('  node sync-database-to-backup.js all                    # 全量同步');
      console.log('  node sync-database-to-backup.js tables table1 table2  # 同步指定表');
      console.log('  node sync-database-to-backup.js verify                # 验证同步结果');
      process.exit(1);
    }

    // 生成同步报告
    await sync.generateSyncReport(syncResults);

    // 验证同步结果
    await sync.verifySync();

    console.log('\n🎉 数据库同步完成！');

  } catch (error) {
    console.error('❌ 同步失败:', error);
    process.exit(1);
  } finally {
    await sync.disconnect();
  }
}

// 处理程序退出
process.on('SIGINT', async () => {
  console.log('\n🛑 同步被中断');
  process.exit(0);
});

// 运行主函数
if (require.main === module) {
  main();
}

module.exports = DatabaseSync;
